package com.chuiyuan.dao;

import com.chuiyuan.db.DBHelper;
import com.chuiyuan.entity.Poem;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by chuiyuan on 2/17/16.
 */
public class PoemDaoImpl implements PoemDao {

    public void add(Poem poem) throws SQLException {
        Connection conn = null ;
        PreparedStatement ps = null ;
        String sql = "insert into poemtable" +
                "(dynasty, category, title, author, content, href, translation)"+
                " values (?, ?, ?, ?, ?, ?, ?)";
        try {
            conn = DBHelper.getConnection() ;
            ps = conn.prepareStatement(sql);
            ps.setString(1,poem.getDynasty());
            ps.setString(2,poem.getCategory());
            ps.setString(3,poem.getTitle());
            ps.setString(4,poem.getAuthor());
            ps.setString(5,poem.getContent());
            ps.setString(6,poem.getHref());
            ps.setString(7,poem.getTranslation());
            ps.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
            throw new SQLException("add poem failed");
        }finally {
            DBHelper.close(null, ps,conn);
        }
    }

    public void update(Poem poem) throws SQLException {
        Connection conn = null;
        PreparedStatement ps = null;
        String sql = "update poemtable set dynasty=?, category=?, title=?," +
                " author=?, content=?, href=? ,translation=? where id=?";
        try {
            conn = DBHelper.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,poem.getDynasty());
            ps.setString(2,poem.getCategory());
            ps.setString(3,poem.getTitle());
            ps.setString(4,poem.getAuthor());
            ps.setString(5,poem.getContent());
            ps.setString(6,poem.getHref());
            ps.setString(7,poem.getTranslation());
            ps.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
            throw new SQLException("update poem failed");
        }finally {
            DBHelper.close(null,ps,conn);
        }
    }

    public void delete(int id) throws SQLException {
        Connection conn = null;
        PreparedStatement ps = null;
        String sql = "delete from poemtable where id=?";
        try {
            conn = DBHelper.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            ps.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
            throw new SQLException("delete poem failed");
        }finally {
            DBHelper.close(null,ps, conn);
        }
    }

    public Poem findById(int id) throws SQLException {
        Connection conn = null ;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Poem poem = null;
        String sql = "select dynasty,catetogry,title,author,content," +
                "href from poemtable where id=?";
        try {
            conn = DBHelper.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            if (rs.next()){
                poem = new Poem() ;
                poem.setDynasty(rs.getString(1));
                poem.setCategory(rs.getString(2));
                poem.setTitle(rs.getString(3));
                poem.setAuthor(rs.getString(4));
                poem.setContent(rs.getString(5));
                poem.setHref(rs.getString(6));
            }
        }catch (SQLException e){
            e.printStackTrace();
            throw new SQLException("find by id failed");
        }finally {
            DBHelper.close(rs,ps, conn);
        }
        return poem;
    }

    public List<Poem> findAll() throws SQLException {
        Connection conn = null ;
        PreparedStatement ps = null ;
        ResultSet rs = null ;

        Poem poem = null ;
        List<Poem> poemList = new ArrayList<Poem>();

        String sql = "select dynasty,catetogry,tie,author,content," +
                "href from poemtable";
        try {
            conn = DBHelper.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                poem = new Poem() ;
                poem.setDynasty(rs.getString(1));
                poem.setCategory(rs.getString(2));
                poem.setTitle(rs.getString(3));
                poem.setAuthor(rs.getString(4));
                poem.setContent(rs.getString(5));
                poem.setHref(rs.getString(6));
                poemList.add(poem);
            }
        }catch (SQLException e){
            e.printStackTrace();
            throw  new SQLException("findAll failed");
        }finally {
            DBHelper.close(rs, ps, conn);
        }
        return poemList ;
    }
}
